package top.went.db.dao;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import top.went.pojo.DeptEntity;
import top.went.pojo.UserEntity;
import top.went.vo.UserVo;

import java.math.BigDecimal;
import java.util.List;

/**
 * 用户dao
 */
public interface UserDao extends CrudRepository<UserEntity, Long> {
    /**
     * 根据用户姓名查找用户
     *
     * @param userName
     * @return
     */
    @Query(nativeQuery = true, value = "select * from TB_USER where user_name = ?1 and user_is_dimission = ?2")
    public UserEntity queryUserByName(String userName, Long userIsDimission);

    /**
     * 根据用户姓名模糊查询
     *
     * @param userName
     * @param dimission
     * @return
     */
//    @Query(nativeQuery = true, value = "select * from TB_USER where user_name like ?1 and user_is_dimission = 1 order by ?#{#pageable}"
//            , countQuery = "select * from TB_USER where user_name like ?1 and user_is_dimission = 0")
//    public List<UserEntity> queryUserLikeName(String userName, Pageable pageable);
    public List<UserEntity> findUserEntitiesByUserNameLikeAndUserIsDimission(String userName, Long dimission, Pageable pageable);

    /**
     * 用户姓名模糊查询有效计数
     *
     * @param userName
     * @param dimission
     * @return
     */
    public long countAllByUserNameLikeAndUserIsDimission(String userName, Long dimission);

    /**
     * 加载，根据用户id查询用户
     *
     * @param userIsDimission
     * @param userId
     * @return
     */
    @Query(nativeQuery = true,
            value = "select * from USER_CRM.TB_USER where USER_IS_DIMISSION = ?1 and USER_ID = ?2")
    public UserEntity findUserByOne(Long userIsDimission, Long userId);

    /**
     * 查询所有在职用户
     *
     * @param userIsDimission
     * @return
     */
//    @Query(nativeQuery = true, value = "select * from USER_CRM.TB_USER where USER_IS_DIMISSION = ?1 order by USER_ID")
//    public List<UserEntity> findAllIsDimission(Long userIsDimission);
    public List<UserEntity> findAllByUserIsDimissionOrderByUserId(Long userIsDimission, Pageable pageable);

    /**
     * 通过密码登录
     *
     * @param userIsDimission
     * @param userName
     * @param userPassword
     * @return
     */
    @Query(nativeQuery = true,
            value = "select * from USER_CRM.TB_USER where USER_IS_DIMISSION = ?1 and USER_NAME = ?2 and USER_PASSWORD ?3")
    public UserEntity loginByPassword(Long userIsDimission, String userName, String userPassword);

    /**
     * 查询指定部门id的所有用户
     *
     * @param deptId
     * @return
     */
    @Query(nativeQuery = true, value = "select * from TB_DEPT left join TB_USER on TB_DEPT.DEPT_ID = TB_USER.DEPT_ID " +
            "where TB_DEPT.DEPT_ID = ?1 and DEPT_IS_DEL = 0 and USER_IS_DIMISSION = 0")
    public List<UserEntity> findUserByDept(Long deptId);

    /**
     * 根据部门加载所有用户
     *
     * @param deptId
     * @return
     */
    @Query(nativeQuery = true, value = "select user_id, user_NAME from TB_USER where DEPT_ID=?1 and USER_IS_DIMISSION=0")
    public List<Object[]> queryAllByDeptId(Long deptId);

    public long countAllByUserIsDimission(Long del);
    /**
     * 查询指定部门的所有用户
     *
     * @param deptEntity
     * @param pageable
     * @return
     */
    public List<UserEntity> findAllByTbDeptByDeptIdAndUserIsDimission(DeptEntity deptEntity, Long del, Pageable pageable);

    public List<UserEntity> findAllByUserIsDimissionAndTbDeptByDeptId(Long del, DeptEntity deptEntity);

    /**
     * 根据部门查用户有效计数
     *
     * @param entity
     * @param del
     * @return
     */
    public long countAllByTbDeptByDeptIdAndUserIsDimission(DeptEntity entity, Long del);

    /**
     * 查询指定用户所拥有的功能id
     *
     * @param userId
     * @return
     */
    @Query(nativeQuery = true, value = "SELECT f.fc_id from TB_USER u,TB_ROLE r,TB_USER_ROLE ur,TB_FUNCTIONS f,ROLE_FC rf " +
            "WHERE u.USER_ID=?1 AND u.USER_IS_DIMISSION=0 AND r.ROLE_IS_DEL=0 AND ur.USER_ROLE_IS_DEL=0 and rf.ROLE_FC_IS_DEL=0 " +
            "AND u.USER_ID=ur.USER_ID AND r.ROLE_ID=ur.ROLE_ID AND r.ROLE_ID=rf.ROLE_ID AND f.FC_ID=rf.FC_ID")
    public List<BigDecimal> queryFcIdByUserId(Long userId);

    /**
     * 根据用户ID分部门查询所有角色，并将该用户拥有的角色标'1'，没有的标'0'
     *
     * @param userId
     * @return
     */
    @Query(nativeQuery = true, value = "select role_id, role_NAME, (case when role_ID in " +
            "(select role_ID from TB_USER_ROLE where user_ID=?1 and user_role_is_del=0) " +
            "then 1 else 0 end) selected from TB_ROLE where ROLE_IS_DEL=0")
    public List<Object[]> queryRolesByUserId(Long userId);
}
